﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Text;
using System.Data;
using Windvoice.Util;
using Windvoice.Blog.Model;

namespace Windvoice.Blog.DataAccess
{
    public class ArticleDAL
    {
        private string ConnectionString = ConfigurationManager.ConnectionStrings["windvoice"].ToString();

        public int Article_Insert(ArticleInfo article)
        {
            string command = String.Format(@"INSERT INTO 
                                                [Article]
                                                (
                                                    [author],
                                                    [title],
                                                    [content],
                                                    [categoryid],
                                                    [publishtime],
                                                    [weather],
                                                    [ishidden],
                                                    [ip],
                                                    [keywords]
                                                ) 
                                                VALUES
                                                ('{0}', '{1}','{2}',{3},'{4}','{5}',{6},'{7}','{8}')",
                                                article.Author,article.Title,article.Content,article.CategoryID,article.PublishTime,article.Weather,article.IsHidden,article.IP,article.KeyWords);
            return AccessHelper.ExecuteNonQuery(ConnectionString,command);
        }

        public int Article_Delete(int ArticleID)
        {
            string command = "DELETE FROM [article] WHERE [id]=" + ArticleID.ToString();
            return AccessHelper.ExecuteNonQuery(ConnectionString,command); 
        }

        public int Article_Update(ArticleInfo article)
        {
            string command = String.Format(@"UPDATE [Article] SET 
                                                [author]='{0}',
                                                [title]='{1}',
                                                [content]='{2}',
                                                [categoryid]={3},
                                                [weather]='{4}',
                                                [ishidden]={5},
                                                [ip]='{6}',
                                                [keywords]='{7}'
                                             WHERE [ID]={8}",
                                                 article.Author,article.Title,article.Content,article.CategoryID,article.Weather,article.IsHidden,article.IP,article.KeyWords,article.ID);
            return AccessHelper.ExecuteNonQuery(ConnectionString,command);
        }

        public int Article_AddHits(int ArticleID)
        {
            string command = "UPDATE Article SET [hits] = [hits] + 1 WHERE [id]=" + ArticleID.ToString();
            return AccessHelper.ExecuteNonQuery(ConnectionString,command);
        }

        public int Article_AddTrackBacks(int ArticleID)
        {
            string command = "UPDATE Article SET [trackbacks] = [trackbacks] + 1 WHERE [id]=" + ArticleID.ToString();
            return AccessHelper.ExecuteNonQuery(ConnectionString,command); 
        }

        public ArticleInfo Articke_Get(int ArticleID)
        {
            ArticleInfo article = null;
            string command = @"SELECT [ID],[author],[title],[content],[categoryid],[publishtime],[weather],[ishidden],[ip],[hits],[trackbacks],[keywords] FROM [Article] WHERE [id]="+ArticleID.ToString();

            DataTable dt = AccessHelper.ExecuteDataSet(ConnectionString,command).Tables[0];
            if (dt.Rows.Count > 0) article = new ArticleInfo(dt.Rows[0]);
            else article = new ArticleInfo();

            return article;
        }

        public DataTable Article_GetList()
        {
            string command = @"SELECT [ID],[author],[title],[content],[categoryid],[publishtime],[weather],[ishidden],[ip],[hits],[trackbacks],[keywords] FROM [Article] WHERE [ishidden]=0";
            return AccessHelper.ExecuteDataSet(ConnectionString, command).Tables[0];
        }

        public DataTable Article_GetList(int PageSize,int PageIndex,int CategoryID,bool IsShowHidden)
        {
            string Condition="", command="";
            if (!IsShowHidden)
            {
                if (CategoryID != 0) Condition = " AND [ishidden]=0 AND [categoryid]=" + CategoryID.ToString();
                else Condition=" AND [ishidden]=0";
            }
            else
            {
                if (CategoryID != 0) Condition = " AND [categoryid]=" + CategoryID.ToString(); 
            }
            

            if(PageIndex==1)
                command = String.Format("SELECT TOP {0} * FROM [Article] WHERE 1=1 {2} ORDER BY [id] DESC", PageSize, (PageIndex - 1) * PageSize, Condition);
            else
                command = String.Format("SELECT TOP {0} * FROM [Article] WHERE [id] NOT IN (SELECT TOP {1} [id] FROM [Article] ORDER BY [id] DESC) {2} ORDER BY [id] DESC",PageSize,(PageIndex-1)*PageSize,Condition);
            
            return AccessHelper.ExecuteDataSet(ConnectionString, command).Tables[0];
        }
    }
}
